﻿//using DBUtil;
//using DBUtil.Provider.SqlServer;
//using System;
//using System.Collections.Generic;
//using System.Data.SqlClient;
//using System.Linq;
//using DotNetCommon.Extensions;
//using NUnit.Framework;
//using Shouldly;
//using System.Threading.Tasks;
//using System.Data.Common;

//namespace Test.SqlServer.Curd.Select
//{
//    [TestFixture]
//    public sealed class SelectTestsAsync : TestBase
//    {
//        #region 测试SelectScalar
//        [Test]
//        public async Task SelectScalarTest()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = now }.ToDictionary(),
//                new { id = 3, name = "小刚" }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            //测试返回int
//            res = await db.SelectScalarAsync<int>("select count(1) from test");
//            Assert.IsTrue(res == 3);
//            //测试返回int? 实际为null
//            var res2 = await db.SelectScalarAsync<int?>("select id from test where name='不存在'");
//            Assert.IsNull(res2);

//            //测试返回int? 实际为2
//            res2 = await db.SelectScalarAsync<int?>("select id from test where name='小花'");
//            Assert.IsNotNull(res2);
//            Assert.IsTrue(res2.Value == 2);

//            //测试返回字符串
//            var name = await db.SelectScalarAsync<string>($"select name from test where id ={db.ParaPrefix}p_id", new { p_id = 1 }.ToDictionary());
//            Assert.IsTrue(name == "小明");

//            //测试返回DateTime
//            var birth = await db.SelectScalarAsync<DateTime>("select birth from test where id=1");
//            Assert.IsTrue(birth.ToString("yyyy-MM-dd") == now.ToString("yyyy-MM-dd"));

//            //测试返回DateTime? 实际为null
//            var birth2 = await db.SelectScalarAsync<DateTime?>("select birth from test where id=3");
//            Assert.IsNull(birth2);
//            //测试返回DateTime? 实际有值
//            birth2 = await db.SelectScalarAsync<DateTime?>("select birth from test where id=2");
//            Assert.IsNotNull(birth2);

//            //测试返回枚举
//            var res3 = await db.SelectScalarAsync<TestEnum>("select id from test where id=1");
//            Assert.IsTrue(res3 == TestEnum.Open);

//            res3 = await db.SelectScalarAsync<TestEnum>("select 0");
//            Assert.IsTrue(res3 == TestEnum.None);

//            var res4 = await db.SelectScalarAsync<TestEnum?>("select 4");
//            Assert.IsNotNull(res4);
//            Assert.IsTrue((int)res4.Value == 4);

//            res4 = await db.SelectScalarAsync<TestEnum?>("select 2");
//            Assert.IsNotNull(res4);
//            Assert.IsTrue(res4.Value == TestEnum.Close);

//            //测试返回bool
//            var res5 = await db.SelectScalarAsync<bool>("select 1");
//            Assert.IsTrue(res5);
//            res5 = await db.SelectScalarAsync<bool>("select 0");
//            Assert.IsFalse(res5);
//            res5 = await db.SelectScalarAsync<bool>("select -1");
//            Assert.IsTrue(res5);
//            res5 = await db.SelectScalarAsync<bool>("select 1");
//            Assert.IsTrue(res5);

//            res5 = await db.SelectScalarAsync<bool>("select 'true'");
//            Assert.IsTrue(res5);
//            res5 = await db.SelectScalarAsync<bool>("select 'false'");
//            Assert.IsFalse(res5);
//            res5 = await db.SelectScalarAsync<bool>("select 'True'");
//            Assert.IsTrue(res5);
//            res5 = await db.SelectScalarAsync<bool>("select 'False'");
//            Assert.IsFalse(res5);

//            //测试返回可空bool
//            var res6 = await db.SelectScalarAsync<bool?>("select 1");
//            Assert.IsNotNull(res6);
//            Assert.IsTrue(res6.Value);
//            res6 = await db.SelectScalarAsync<bool?>("select 0");
//            Assert.IsNotNull(res6);
//            Assert.IsFalse(res6.Value);

//            res6 = await db.SelectScalarAsync<bool?>("select null");
//            Assert.IsNull(res6);

//            res6 = await db.SelectScalarAsync<bool?>("select id from test where id =-1");
//            Assert.IsNull(res6);
//        }

//        [Test]
//        public async Task SelectScalarTest2()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = now }.ToDictionary(),
//                new { id = 3, name = "小刚" }.ToDictionary());
//            Assert.IsTrue(res == 3);

//            var res2 = await db.SelectScalarAsync<int?>("select id from test where name =@name", new { name = "小花" }.ToDictionary());
//            Assert.IsNotNull(res2);
//            Assert.IsTrue(res2.Value == 2);

//            res2 = await db.SelectScalarAsync<int?>("select id from test where name =@name", new { name = "小明" }.ToDictionary());
//            Assert.IsNotNull(res2);
//            Assert.IsTrue(res2.Value == 1);

//            res2 = await db.SelectScalarAsync<int?>("select id from test where name ='小刚'");
//            Assert.IsNotNull(res2);
//            Assert.IsTrue(res2.Value == 3);

//            //测试参数
//            db.Manage.DropProcedureIfExist("usp_test");
//            db.ExecuteSql(@"create proc usp_test
//@userid int =1,
//@pwd varchar(50) output
//as
//set nocount on;
//begin
//	declare @age int=18
//    select @userid as id,@age as age,'小明' as name
//	set @pwd=CONVERT(varchar,@userid)+'_'+'123456'
//end");

//            var pwd = db.CreatePara("pwd", null, System.Data.ParameterDirection.Output, size: 50);
//            var userid = db.CreatePara("userid", 1);
//            var result = await db.SelectScalarAsync<int>("usp_test", System.Data.CommandType.StoredProcedure, 30, new DbParameter[] { userid, pwd });
//            result.ShouldBe(1);
//            pwd.Value.ShouldBe("1_123456");
//        }
//        #endregion

//        #region 测试SelectDataTable
//        [Test]
//        public async Task SelectDataTableTest()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = now }.ToDictionary(),
//                new { id = 3, name = "小刚", birth = now }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            var dt = await db.SelectDataTableAsync("select * from test where id>1");
//            Assert.IsNotNull(dt);
//            Assert.IsTrue(dt.Rows.Count == 2);
//            Assert.IsTrue(dt.Rows[1]["id"].ToString() == "3");
//            Assert.IsTrue(dt.Rows[1]["name"].ToString() == "小刚");

//            dt = await db.SelectDataTableAsync("select * from test where id>@id", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(dt);
//            Assert.IsTrue(dt.Rows.Count == 2);
//            Assert.IsTrue(dt.Rows[1]["id"].ToString() == "3");
//            Assert.IsTrue(dt.Rows[1]["name"].ToString() == "小刚");

//            dt = await db.SelectDataTableAsync("select * from test where id>@id", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(dt);
//            Assert.IsTrue(dt.Rows.Count == 2);
//            Assert.IsTrue(dt.Rows[1]["id"].ToString() == "3");
//            Assert.IsTrue(dt.Rows[1]["name"].ToString() == "小刚");
//        }
//        #endregion

//        #region 测试SelectDataSet
//        [Test]
//        public async Task SelectDataSetTest()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = now }.ToDictionary(),
//                new { id = 3, name = "小刚", birth = now }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            var ds = await db.SelectDataSetAsync("select * from test where id>1;select * from test where id=1;");
//            Assert.IsNotNull(ds);
//            Assert.IsTrue(ds.Tables.Count == 2);
//            Assert.IsTrue(ds.Tables[0].Rows.Count == 2);
//            Assert.IsTrue(ds.Tables[1].Rows.Count == 1);
//            Assert.IsTrue(ds.Tables[0].Rows[1]["id"].ToString() == "3");
//            Assert.IsTrue(ds.Tables[1].Rows[0]["name"].ToString() == "小明");

//            ds = await db.SelectDataSetAsync("select * from test where id>@id;select * from test where id=@id2;", new { id = 1, id2 = 1 }.ToDictionary());
//            Assert.IsNotNull(ds);
//            Assert.IsTrue(ds.Tables.Count == 2);
//            Assert.IsTrue(ds.Tables[0].Rows.Count == 2);
//            Assert.IsTrue(ds.Tables[1].Rows.Count == 1);
//            Assert.IsTrue(ds.Tables[0].Rows[1]["id"].ToString() == "3");
//            Assert.IsTrue(ds.Tables[1].Rows[0]["name"].ToString() == "小明");

//            ds = await db.SelectDataSetAsync("select * from test where id>@id;select * from test where id=@id;", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(ds);
//            Assert.IsTrue(ds.Tables.Count == 2);
//            Assert.IsTrue(ds.Tables[0].Rows.Count == 2);
//            Assert.IsTrue(ds.Tables[1].Rows.Count == 1);
//            Assert.IsTrue(ds.Tables[0].Rows[1]["id"].ToString() == "3");
//            Assert.IsTrue(ds.Tables[1].Rows[0]["name"].ToString() == "小明");
//        }
//        #endregion

//        #region 测试SelectDataReader
//        [Test]
//        public async Task SelectDataReaderTest()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = now }.ToDictionary(),
//                new { id = 3, name = "小刚" }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            await db.SelectDataReaderAsync(async reader =>
//            {
//                Assert.IsTrue(reader.FieldCount == 3);
//                var index = 0;
//                while (await reader.ReadAsync())
//                {
//                    var id = reader.GetInt32(0);
//                    var name = reader.GetString(1);
//                    var birth = reader["birth"];
//                    if (index == 0)
//                    {
//                        Assert.IsTrue(id == 1);
//                        Assert.IsTrue(name == "小明");
//                    }
//                    else if (index == 1)
//                    {
//                        Assert.IsTrue(id == 2);
//                        Assert.IsTrue(name == "小花");
//                    }
//                    else if (index == 2)
//                    {
//                        Assert.IsTrue(id == 3);
//                        Assert.IsTrue(name == "小刚");
//                        Assert.IsTrue(birth is DBNull || birth is null);
//                    }
//                    index++;
//                }
//            }, "select * from test");

//            await db.SelectDataReaderAsync(async reader =>
//           {
//               Assert.IsTrue(reader.FieldCount == 3);
//           }, "select * from test where id>@id", new { id = 1 }.ToDictionary());

//            await db.SelectDataReaderAsync(async reader =>
//            {
//                Assert.IsTrue(reader.FieldCount == 3);
//            }, "select * from test where id>@id", new { id = 1 }.ToDictionary());
//        }
//        #endregion

//        #region 测试SelectDicionary

//        [Test]
//        public async Task SelectDicionaryTest()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = now }.ToDictionary(),
//                new { id = 3, name = "小刚" }.ToDictionary());
//            Assert.IsTrue(res == 3);

//            //多行数据
//            var dics = await db.SelectDictionaryListAsync("select * from test");
//            Assert.IsNotNull(dics);
//            Assert.IsTrue(dics.Count == 3);
//            var row = dics[0];
//            Assert.IsTrue(row["id"].ToString() == "1");
//            Assert.IsTrue(row["name"].ToString() == "小明");
//            Assert.IsTrue(row["birth"] is DateTime);

//            row = dics[2];
//            Assert.IsTrue(row["id"].ToString() == "3");
//            Assert.IsTrue(row["name"].ToString() == "小刚");
//            Assert.IsTrue(row["birth"] is null || row["birth"] is DBNull);

//            dics = await db.SelectDictionaryListAsync("select * from test where id>@id", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(dics);
//            Assert.IsTrue(dics.Count == 2);

//            dics = await db.SelectDictionaryListAsync("select * from test where id=@id", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(dics);
//            Assert.IsTrue(dics.Count == 1);

//            //单行数据
//            var dic = await db.SelectDictionaryAsync("select * from test where id=1");
//            Assert.IsNotNull(dic);
//            Assert.IsTrue(dic.Count == 3);
//            Assert.IsTrue(dic["id"].ToString() == "1");
//            Assert.IsTrue(dic["name"].ToString() == "小明");
//            Assert.IsTrue(dic["birth"] is DateTime);

//            dic = await db.SelectDictionaryAsync("select * from test where id=@id", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(dic);
//            Assert.IsTrue(dic.Count == 3);
//            Assert.IsTrue(dic["id"].ToString() == "1");
//            Assert.IsTrue(dic["name"].ToString() == "小明");
//            Assert.IsTrue(dic["birth"] is DateTime);

//            dic = await db.SelectDictionaryAsync("select * from test where id=@id", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(dic);
//            Assert.IsTrue(dic.Count == 3);
//            Assert.IsTrue(dic["id"].ToString() == "1");
//            Assert.IsTrue(dic["name"].ToString() == "小明");
//            Assert.IsTrue(dic["birth"] is DateTime);

//        }
//        #endregion

//        #region 测试SelectModel
//        [Test]
//        public async Task SelectModelTest()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
//                new { id = 3, name = "小刚", birth = DateTime.Now }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            //测试SelectModels
//            var models = await db.SelectModelListAsync<Person>("select * from test");
//            Assert.IsTrue(models.Count == 3);
//            Assert.IsTrue(models.Last().Id == 3);

//            models = await db.SelectModelListAsync<Person>("select * from test where id=@id", new { id = 1 }.ToDictionary());
//            Assert.IsTrue(models.Count == 1);
//            Assert.IsTrue(models.Last().Id == 1);

//            models = await db.SelectModelListAsync<Person>("select * from test where id=@id", new { id = 2 }.ToDictionary());
//            Assert.IsTrue(models.Count == 1);
//            Assert.IsTrue(models.Last().Id == 2);


//            //测试SelectModel
//            var model = await db.SelectModelAsync<Person>("select * from test");
//            Assert.IsNotNull(model);
//            Assert.IsTrue(model.Id == 1);

//            model = await db.SelectModelAsync<Person>("select * from test where id=@id", new { id = 1 }.ToDictionary());
//            Assert.IsNotNull(model);
//            Assert.IsTrue(model.Id == 1);

//            model = await db.SelectModelAsync<Person>("select * from test where id=@id", new { id = 3 }.ToDictionary());
//            Assert.IsNotNull(model);
//            Assert.IsTrue(model.Id == 3);
//        }
//        #endregion

//        #region 测试参数化查询 in & like & between...and
//        /// <summary>
//        /// 参数化in
//        /// </summary>
//        [Test]
//        public async Task ParameterIn()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
//                new { id = 3, name = "小刚" }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            //参数化in
//            var persons = await db.SelectModelListAsync<Person>("select * from test where id in (@id1,@id2)", new { id1 = 1, id2 = 2 }.ToDictionary());
//            Assert.IsTrue(persons.Count == 2);
//        }

//        /// <summary>
//        /// 参数化like
//        /// </summary>
//        [Test]
//        public async Task ParameterLike()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
//                new { id = 3, name = "小刚" }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            //参数化in
//            var persons = await db.SelectModelListAsync<Person>("select * from test where name like @name", new { name = "%花%" }.ToDictionary());
//            Assert.IsTrue(persons.Count == 1);
//        }

//        /// <summary>
//        /// 参数化Between...and
//        /// </summary>
//        [Test]
//        public async Task ParameterBetween()
//        {
//            //准备数据
//            if (db.IsTableExist("test")) db.Manage.DropTable("test");
//            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
//            var now = DateTime.Now;
//            var res = db.Insert("test",
//                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
//                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
//                new { id = 3, name = "小刚" }.ToDictionary());
//            Assert.IsTrue(res == 3);
//            //参数化in
//            var persons = await db.SelectModelListAsync<Person>("select * from test where birth between @start and @end", new { start = DateTime.Now.AddDays(-1), end = DateTime.Now }.ToDictionary());
//            Assert.IsTrue(persons.Count == 1);
//            Assert.IsTrue(persons.FirstOrDefault().Id == 1);
//        }
//        #endregion
//    }
//}
